﻿using System;

using NPOI.HSSF;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using System.Collections;
using System.IO;
using System.Data; 

namespace CreateBigDate
{
    public class Excel
    {
        private HSSFWorkbook _workBook;
        private ISheet _wbSheet = null;
        private DataColumnCollection _columns = null;


        private int _col = 0;    //total columns 
        private int _row = 0;    //total rows 
        private int _sheet = 0;  //total sheets 
        private int _sheetRowNum = 65536;  //each sheet allow rows 

        public Excel()
        {
            InstanceWorkBook();
        }

        /// <summary> 
        ///  实例方法 
        /// </summary> 
        /// <param name="sheetRowNum">单个表单允许的最大行数</param> 
        public Excel(int sheetRowNum)
        {
            _sheetRowNum = sheetRowNum;
            InstanceWorkBook();
        }

        /// <summary> 
        /// 实例方法 
        /// </summary> 
        /// <param name="columns">表头</param> 
        public Excel(DataColumnCollection columns)
        {
            _columns = columns;
            InstanceWorkBook();
        }

        private void InstanceWorkBook()
        {
            /////cretate WorkBook 
            _workBook = new HSSFWorkbook();

            var dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "BaiyiTimes";
            _workBook.DocumentSummaryInformation = dsi;

            ////create a entry of SummaryInformation 
            var si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "Etimes Secure Document System Log Backup";
            _workBook.SummaryInformation = si;
        }

        private DataColumnCollection GetColumns(DataColumnCollection columns)
        {
            return columns == null || columns.Count == 0 ? _columns : columns;
        }

        private ISheet GetSheet(ISheet sheet)
        {
            return sheet == null ? _wbSheet : sheet;
        }

        private void CreateHeader(ISheet sheet, DataColumnCollection columns)
        {
            _columns = GetColumns(columns);

            /////create row of column 
            var oRow = sheet.CreateRow(0);

            foreach (DataColumn column in _columns)
            {
                var oCell = oRow.CreateCell(_col);

                var style1 = _workBook.CreateCellStyle();
                style1.FillForegroundColor = HSSFColor.Blue.Index2;
                style1.Alignment = HorizontalAlignment.Center;
                style1.VerticalAlignment = VerticalAlignment.Center;

                var font = _workBook.CreateFont();
                font.Color = HSSFColor.White.Index;
                style1.SetFont(font);

                oCell.CellStyle = style1;
                var name = column.ColumnName;
                oCell.SetCellValue(name.ToString());

                _col++;
            }
            ///// header belong to rows 
            _row++;
        }

        private void CreateHeader(ISheet sheet)
        {
            CreateHeader(sheet, null);
        }

        public ISheet CreateSheet()
        {
            return CreateSheet(null);
        }

        public ISheet CreateSheet(DataColumnCollection columns)
        {
            _wbSheet = _workBook.CreateSheet((_sheet + 1).ToString());
            CreateHeader(_wbSheet, columns);
            _sheet++;
            return _wbSheet;
        }

        public void SetRowValue(DataRowCollection rows, ISheet sheet)
        {
            _wbSheet = GetSheet(sheet);
            foreach (DataRow row in rows)
            {
                SetRowValue(row);
            }
        }

        public void SetRowValue(DataRowCollection rows)
        {
            SetRowValue(rows, null);
        }

        public void SetRowValue(DataRow row)
        {
            // create a new sheet 
            if (_row % _sheetRowNum == 0)
            {
                CreateSheet();
            }
            var oRow = _wbSheet.CreateRow(_row % _sheetRowNum);
            var obj = string.Empty;
            var cell = 0;
            foreach (DataColumn column in _columns)
            {
                obj = row[column.ColumnName].ToString();
                oRow.CreateCell(cell).SetCellValue(obj);
                cell++;
            }
            _row++;
        }

        public void SetProtectPassword(string password, string username)
        {
            _workBook.WriteProtectWorkbook(password, username);
        }

        public void SaveAs(string filePath)
        {
            if (File.Exists(filePath)) File.Delete(filePath);
            var file = new FileStream(filePath, FileMode.Create);
            _workBook.Write(file);
            file.Close();
        }
    } 
}
